IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetArraPIQuestionnairesByAdmPersonId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.GetArraPIQuestionnairesByAdmPersonId
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/***************************************************************                            
* Name:   [GetArraPIQuestionnairesByAdmPersonId]                            
* Purpose:  Retrieves the Dislcosures for COI Users matching the filter parameters.
* @admPersonIds     admPersonIds of the users                                      
* @COIOrgIds		COI Organization Ids
* @OrderBy			OrderBy column
* @PageSize			Page size, generally the number of columns to retrieve 
* @PageStartIndex	Starting page number
* @PageEndIndex		Ending page number
*********************************************************************/    
CREATE PROCEDURE [dbo].[GetArraPIQuestionnairesByAdmPersonId]                                
(                                
@PIAdmPersonId VARCHAR(2000) = null,                                
@OrderBy VARCHAR(20) = NULL,
@PageSize INT = 10,
@PageStartIndex INT = 0,
@PageEndIndex INT = 1
)                                
AS BEGIN                                
	SET NOCOUNT ON 

	DECLARE @tempTable TABLE (Id INT,FirstName VARCHAR(50),LastName VARCHAR(50),EmailAddress VARCHAR(100),DepartmentManagerAdmPersonId INT)
	INSERT INTO @tempTable EXEC dbo.GetArraPIsByDAAdmPersonId @PIAdmPersonId
	INSERT INTO @tempTable (Id) VALUES (@PIAdmPersonId)

	SELECT 
	afct.PrincipalInvestigatorId,afct.PILastName + ', ' + afct.PIFirstName  AS PIName,afct.FundNumber,afct.SponsorNumber,
	aqrs.QuarterStartDate,aqrs.QuarterEndDate,
	apiq.Id AS 'PIQuestionnaireId',apiq.Narrative,--,apiq.QuarterlyActivities,
	apiqeh.ArraPIQuestionnaireEventID,dbo.GetArraNotificationDateByPIQuestionnaireId(apiq.Id) AS 'LastNotified',
    apiqeh.EventDate,
	apiqe.[Name] AS 'PIQuestionnaireStatus',apiqe.Id as 'PIQuestionnaireStatusId'
	FROM agrAgreement_fct afct
	INNER JOIN ArraAgreement aagmt ON aagmt.FundId = afct.FundId
	INNER JOIN ArraQuarterlyReportStatus aqrs ON aqrs.Id = aagmt.ArraQuarterlyReportStatusId
	INNER JOIN ArraPIQuestionnaire apiq ON apiq.ArraAgreementId = aagmt.Id
	INNER JOIN ArraPIQuestionnaireEventHistory apiqeh ON (apiqeh.ArraPIQuestionnaireId = apiq.Id
			AND apiqeh.EventDate = (SELECT MAX(EventDate) FROM ArraPIQuestionnaireEventHistory WHERE ArraPIQuestionnaireID = apiq.Id  AND ArraPIQuestionnaireEventId NOT IN(1,5)))--Eliminates the Notfication event types.
	INNER JOIN ArraPIQuestionnaireEvent apiqe ON apiqe.Id = apiqeh.ArraPIQuestionnaireEventId 
	INNER JOIN ArraPIQuestionnaireEventType apiqet ON apiqet.Id = apiqe.ArraPIQuestionnaireEventTypeID
	WHERE
	afct.PrincipalInvestigatorId IN (SELECT Id FROM @tempTable)
	ORDER BY PIName
	
END

--EXEC GetArraPIQuestionnairesByAdmPersonId @PIAdmPersonId=1040 68843 22813

